<?php

define ('GET_ID_BY_ALIAS','SELECT id FROM menu WHERE alias = ?');
define ('CHECK_PARENT','SELECT * FROM `menu_relations`WHERE id =(SELECT menu.id FROM menu WHERE alias = ?)  AND parent_id = (SELECT menu.id FROM menu WHERE alias = ?)');
define ('GET_MENU','SELECT * FROM menu ' .
		'LEFT JOIN (SELECT * FROM users_rights WHERE user_id = ?) AS partitions ' .
		'USING ( id ) LEFT JOIN menu_relations USING ( id ) ' .
		'WHERE `type`="default" OR `type`="plugin" ORDER BY order_id');

define ('GET_FULL_MENU','SELECT * FROM menu ' .
		'LEFT JOIN (SELECT * FROM users_rights WHERE user_id = ?) AS partitions ' .
		'USING ( id ) LEFT JOIN menu_relations USING ( id ) ' .
		'ORDER BY order_id');

define ('GET_LEVEL','SELECT * FROM menu, menu_relations WHERE menu.id = menu_relations.id AND visibility = 1 AND parent_id = ? ORDER BY order_id');

define ('GET_FIRST_LEVEL','SELECT * FROM menu, menu_relations WHERE menu.id = menu_relations.id AND visibility = 1 ORDER BY order_id');

//
define ('GET_SYSTEM_ITEMS','SELECT * FROM `menu` , `menu_relations` WHERE `type` = \'system\' AND `menu`.`id` = `menu_relations`.`id`');


define ('SET_VISIBILITY','UPDATE `menu` SET `visibility` = ? WHERE `id` = ?');

define ('CREATE_RELATIONS_TMP','CREATE TABLE `menu_relations_tmp` (`id` INT NOT NULL ,`parent_id` INT NOT NULL ,`order_id` INT NOT NULL , PRIMARY KEY ( `order_id` ) , INDEX ( `id` , `parent_id` )) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_bin');
define ('TRUNCATE_RELATIONS_TMP','TRUNCATE TABLE `menu_relations_tmp`');
//define ('INSERT_RELATION',"INSERT INTO `menu_relations_tmp` (`id` ,`parent_id`, `order_id`) VALUES ('%s', '%s', '%s')");
define ('INSERT_RELATION','INSERT INTO `menu_relations_tmp` (`id` ,`parent_id`, `order_id`) VALUES (?, ?, ?)');

define ('DROP_OLD_TABLE','DROP TABLE `menu_relations_old`');
define ('RENAME_TO_OLD','RENAME TABLE menu_relations TO menu_relations_old');
define ('RENAME_TMP','RENAME TABLE menu_relations_tmp TO menu_relations');
class Model_Menu extends Models_Base {
	protected $items_count_full;
	protected $firstIndex;
	// gets first level of menu
	
	
	function find() {
		
	}
	
	function getIdByAlias($alias) {
	
		try{	
			$sth = $this->dbh->prepare(GET_ID_BY_ALIAS);
			$sth->execute(array($alias));
			$data = $sth->fetch();
		} catch (PDOException $e) {
			error_log ("DB Error![getIdByAlias] : " . $e->getMessage());
			return array(
				status 		=> 302,
				location	=> '/admin/error?db'
			);
 		}
		
		return $data[id];
	}

	function checkParent($parent,$child) {
		try{
			$sth = $this->dbh->prepare(CHECK_PARENT);
			$sth->execute(array($child,$parent));
			$data = $sth->fetch();
		} catch (PDOException $e) {
			error_log ("DB Error![checkParent]: " . $e->getMessage());
			return array(
				status 		=> 302,
				location	=> '/admin/error?db'
			);
 		}

		return $data ? 1 : 0;
	}
	
	
	function getMenu ($id) {

		try {
			$args = array();
			$args[] = $id ? $id : 1;
			
			$sth = $this->dbh->prepare(GET_LEVEL);
			$sth->execute($args);
			
			while($row = $sth->fetchObject()) {
				$menu[] = $row;
			}				

		} catch (PDOException $e) {
			error_log ("DB Error![getMenu]: " . $e->getMessage());
			return array(
				status 		=> 302,
				location	=> '/admin/error?db'
			);
 		}
		
		return $menu;
	}

	function getMenuArray ($id) {

		try {
			$args = array();
			$args[] = $id ? $id : 1;
			
			$sth = $this->dbh->prepare(GET_LEVEL);
			$sth->execute($args);
			
			return $sth->fetchAll();
			
//			while($row = $sth->fetchObject()) {
//				$menu[] = $row;
//			}				

		} catch (PDOException $e) {
			error_log ("DB Error![getMenu]: " . $e->getMessage());
			return array(
				status 		=> 302,
				location	=> '/admin/error?db'
			);
 		}
	}



	function getMenuPDO($user_id) {

		try{
			$sth = $this->dbh->prepare(GET_MENU);
			$sth->execute(array($user_id));

		} catch (PDOException $e) {
			error_log ("DB Error[getMenuPDO]!: " . $e->getMessage());
			error_log ();
			return array(
				status 		=> 302,
				location	=> '/admin/error?db'
			);
 		}
		
		$this->items_count_full = 0;
		
		while($row = $sth->fetchObject()) {
			$menu[] = $row;
			$this->items_count_full++;
		}

		return $menu;
	}

	function getMenuFullPDO($user_id) {

		try{
			$sth = $this->dbh->prepare(GET_FULL_MENU);
			$sth->execute(array($user_id));
		} catch (PDOException $e) {
			error_log ("DB Error[getMenuFullPDO]!: " . $e->getMessage());
			return array(
				status 		=> 302,
				location	=> '/admin/error?db'
			);
 		}
		
		$this->items_count_full = 0;
		
		while($row = $sth->fetchObject()) {
			$menu[] = $row;
			$this->items_count_full++;
		}

		return $menu;
	}
	

	function getHierarchicalMenu($menu_args) {
		
		$items = $menu_args[show] === 'all' 
			? $this->getMenuFullPDO($menu_args[uid]) 
			: $this->getMenuPDO($menu_args[uid]);

//print "<pre>";
//print_r($items);


		// creating new array for JSON
		$output = array ();

		// number of the elements
		$output['count'] 			= $this->count_full();
		
		// id of the first element
		$output['firstIndex'] 	= $this->firstIndex();
		
		// column title
		$output['columns']		= array("\"Название раздела\"");
//		$output['info']				= array("\"Название раздела\"");
//		$output['id']				= 0;
			
		foreach ($items as $key => $value) {
			// going to the top of the tree
			$parent = 0;

			// get elements recursive				
			$level = $this->addLevel($value,$parent,$items);
//$this->registry['debug']->warn($level);
			// adding to array if not empty 
			if ($level)	
				$output['items'][] =  $level;
		}		

		if($menu_args[show] == 'all') {
			return $output;
		} else {
			return array(
 				count			=> $output[count],
    			firstIndex		=> $output[firstIndex],
    			columns			=> $output[columns],
    			items			=> $output[items][0][children]			
			);
		}
		
	}

		//private recursive method [for load json]
		function addLevel ($item,$parent,$flat_array) {
			
			$new = array();
			
			if($item->parent_id == $parent) {
				$new[id]         = $item->id;
				$new[info]       = array("\"$item->value\"");
				$new[parent]     = $item->parent_id;
				$new[alias]      = "\"$item->alias\"";
				$new[visible]    = $item->visibility;
				$new[checked]    = $item->user_id ? 1 : 0 ;
				
				//	array_shift($flat_array);
				foreach($flat_array as $k => $v) {
					$children = $this->addLevel($v,$new[id],$flat_array);

					if($children) $new[children][] = $children;
				}
				return $new;	
			}
		
			return;
		}
	
	function count_full() {
		return $this->items_count_full;
	}
	
	function firstIndex() {
		

		return $this->firstIndex;
	}
	
	private function makeTree($items) {
		$output = array();

		foreach ($items as $item) {
			$output[] = $this->addNode($item);
		}
		return $output;
	}
	
	private function addNode($node) {
		$new = array();
		
		$new[id] 		= $node->id;
		$new[value]	= $node->value;
		
		return $new;
	}
/*	
	function getPluginName ($alias) {
		$menu = array();
		
		$sql = sprintf("SELECT value FROM menu WHERE alias = '%s'",mysql_real_escape_string($alias));
		$result = mysql_query($sql,$this->db);
		
		if($result) {
			while($row = mysql_fetch_object($result) ) {
				return $row->value;				
			}
		}
	}	
*/	
	
//	function getIdByAlias ($alias) {
//		$sql = sprintf("SELECT id FROM menu WHERE alias = '%s'",mysql_real_escape_string($alias));	
//
//		$result = mysql_query($sql,$this->db);
//		
//		if($result) {
//			$row = mysql_fetch_object($result);
//		}
//		
//		/*if(empty($row->id)){
//			$sql = "SELECT id FROM menu WHERE alias = 'index' ";	
//			$result = mysql_query($sql,$this->db);
//			$row = mysql_fetch_object($result);			
//		}	*/	
//		
//		return $row->id;
//	}
	
	function setVisibility($id,$value) {

		try{
			$sth = $this->dbh->prepare(SET_VISIBILITY);
	
			return $sth->execute(array($value, $id));
		} catch (PDOException $e) {
			error_log ("DB Error![setVisibility]: " . $e->getMessage());
			return array(
				status 		=> 302,
				location	=> '/admin/error?db'
			);
 		}		
/*		
		
		$sql = sprintf("UPDATE `menu` SET `visibility` = '%s' WHERE `id` = %d",
		mysql_real_escape_string($value),
		mysql_real_escape_string($id)
		);
		$result = mysql_query($sql,$this->db);
*/
	}
	
	function saveMenu($id,$args) {
		$set = '';

		foreach($args as $key => $value) {
			$set .= " `$key` = '$value', ";
		}	
        $set .= " `id`= $id ";
		$sql = "update `menu` set ".$set." where `id`=$id ";
//		$this->registry['debug']->warn($sql);

		$this->dbh->query($sql);
//		$result = mysql_query($sql,$this->db);		
	}
	
	function addNewRelations ($items) {
 	
 	try{
		
		// creating new temporary table
		$this->dbh->query(CREATE_RELATIONS_TMP);
		// clean table 
		$this->dbh->query(TRUNCATE_RELATIONS_TMP);
/*
		$sql = "CREATE TABLE `menu_relations_tmp` (
		`id` INT NOT NULL ,
		`parent_id` INT NOT NULL ,
		`order_id` INT NOT NULL ,
		PRIMARY KEY ( `order_id` ) ,
		INDEX ( `id` , `parent_id` ) 
		) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_bin";
		$result = mysql_query($sql, $this->db);

	
		$result = mysql_query("TRUNCATE TABLE `menu_relations_tmp`",$this->db);
*/		
		// inserting data
		// add root
		$items[] = array(id => 1, parent=> 0, order => 0);
		$sth = $this->dbh->prepare(INSERT_RELATION);
		foreach($items as $item) {
			$sth->execute(array($item[id],$item[parent],$item[order]));
/*			
			$sql = sprintf(INSERT_RELATION,$item[id],$item[parent],$item[order]);
			$result = mysql_query($sql,$this->db);
*/
		}


		//deleting old table
		$this->dbh->query(DROP_OLD_TABLE);
//		$result = mysql_query("DROP TABLE `menu_relations_old`",$this->db);
				
		// renaming old table
		$this->dbh->query(RENAME_TO_OLD);
//		$result = mysql_query("RENAME TABLE menu_relations TO menu_relations_old",$this->db);
		
		// renaming new table
		$this->dbh->query(RENAME_TMP);
//		$result = mysql_query("RENAME TABLE menu_relations_tmp TO menu_relations",$this->db);
		} catch (PDOException $e) {
			error_log ("DB Error![addNewRelations]: " . $e->getMessage());
			return array(
				status 		=> 302,
				location	=> '/admin/error?db'
			);
 		}
	}
	
	function getSystemItems() {

		try{
			$sth = $this->dbh->prepare(GET_SYSTEM_ITEMS);
	
			$sth->execute();
	
			while ($row = $sth->fetch()) {
				$items[] = array(id => $row[id],parent => $row[parent_id],order => $row[order_id]);
			}

		} catch (PDOException $e) {
			error_log ("DB Error![getSystemItems]: " . $e->getMessage());
			return array(
				status 		=> 302,
				location	=> '/admin/error?db'
			);
 		}
		return $items;		
	}
	
	function getBranch($id_start, $levels) {
		
		$out = array();
		$out = $this->getMenuArray($id_start);
	
			
		foreach($out as $key => $value ) {
			$out[$key][children] = $this->getMenuArray($value[id]);
		}
			
		return $out;	
	}
}
?>
